Hello,

I have a table (visits):

"visit_id , Site name, Site_date"

Now another table (actions):

"visit_id,action_name"

Can I choose actions based on visit ids from the visit table, soemthing like "SELECT * FROM `actions` WHERE `site_date` in `visits` = '$date'?

Is that possible,

I was thinking of creating a date for the action table, but if the above is possible I dont see a reason why.

Thanks,

Peace,

SELECT * FROM actions, visits WHERE Site_date=$date

That's it :)

nevermind......

SELECT * FROM actions, visits WHERE Site_date=$date

That's it :)

I have to add something upon the query, site_id (which is a field in both table) in the WHERE clause:

SELECT * FROM actions, visits WHERE Site_date=$date AND site_id=2

Now its giving me an error #1052 - Column 'site_id' in where clause is ambiguous

I tried doing actions.site_id/visits.actions, but it would return all the fields no matter what the site id is?

Never knew the query would be that simple.

Thank you again,

So are you still having problems with something? I can't tell if you need help or are just telling us something. If you need help... explain again your question :)

its ambiguous because you didnt tell mysql which table to get the site id from (because there are two fields in different tables with the same name)

so when referencing it in the where clause you need to specify the table, such as actions.site_id or visits.site_id

Didn't see site_id in your original specs. Do you mean visit_id? If so, you can alias field names and table names, and do a join for more clarity.

SELECT v.visit_id, v.Site_name v.Site_date, a.action_name FROM visits v LEFT OUTER JOIN actions a ON v.visit_id=a.visit_id WHERE v.visit_id=$visit_id AND v.Site_Date='$date'

Again, conjecture since I'm not sure of the columns in your tables.

its ambiguous because you didnt tell mysql which table to get the site id from (because there are two fields in different tables with the same name)

so when referencing it in the where clause you need to specify the table, such as actions.site_id or visits.site_id

Sorry for not making the point clear at the second post.

I removed the field site_id from the actions sites, but when I made the query, it would return all actions when using SELECT COUNT(*) and only 5 rows when using SELECT * .

There should be around 159 rows instead.

To make everything clear, here is the full table structure:

Table search_keywords:

visit_id, engine_name, search_term

then there is:

visitors_referrals table:

which has:

visit_id, site_id, user_ip, user_enters

The query I did based on what you helped me with after removing site_id from search_keywords:


SELECT `engine_name`,COUNT(*) FROM `visitors_referrals`,`search_keywords` WHERE visitors_referrals.site_id = '10' AND `engine_name` != '' AND `user_enters` >= '2006-01-00 00:00:00' AND `user_enters` < '2006-2-00 00:00:00' GROUP BY `engine_name`


That query returns COUNT (very large numbers), If I use SELECT * it would only return five rows.

If I was to do this:


SELECT * FROM `visitors_referrals` WHERE `site_id` = '10' AND `search_keywords.site_id` = '10' AND `engine_name` != '' AND `user_enters` >= '2006-01-00 00:00:00' AND `user_enters` < '2006-2-00 00:00:00'


That query would return the exact number.

Peace,

Didn't see site_id in your original specs. Do you mean visit_id? If so, you can alias field names and table names, and do a join for more clarity.

SELECT v.visit_id, v.Site_name v.Site_date, a.action_name FROM visits v LEFT OUTER JOIN actions a ON v.visit_id=a.visit_id WHERE v.visit_id=$visit_id AND v.Site_Date='$date'

Again, conjecture since I'm not sure of the columns in your tables.


I tried this:


SELECT * FROM visitors_referrals v LEFT OUTER JOIN search_keywords a ON v.site_id=a.site_id WHERE v.site_id=10 AND v.user_enters >= '2006-01-00 00:00:00' AND v.user_enters < '2006-2-00 00:00:00'


Which seems to return all rows, except that each row is repeated a hundread times or more?

Tried to add a.site_id=10 (in where clause) but would return the same as before.

Thanks and sorry for not making it clear the firs time,

Peace,

Ok, thanks again guys, but it seems that I was sleeping too much..

The query Christopher said (plus some more- matching visit id also, worked perfectly).:


SELECT * FROM visitors_referrals v LEFT OUTER JOIN search_keywords a ON v.visit_id=a.visit_id WHERE a.site_id=10 AND v.site_id=10 AND v.user_enters >= '2006-01-00 00:00:00' AND v.user_enters < '2006-2-00 00:00:00'


Thanks again to all of you who participated,

Peace,

Again, you're joining on site_id, but you reply states it isnt in there, so there is nothing to join on.

The assumption:

table that doesnt have site_id => search_keywords

If that is true, you cant join on it. You have to join on the visit_id as the PK:FK relationship, if any exists between those two.
---

Boy, you're a quick typist! glad you got that one. Joins are a PAIN to look at, one can go cross-eyed!