[SOLVED] combining queries

[esport]

Hi Guys,

 

I have a table of users which I want to display all. Easy done

 

SELECT userid,name FROM users

 

I have another table that groups the users in categories.

 

What I want to do, is display all the users in the users table but at the same time I want to tag the users in a specific category. For example.

 

SELECT userid FROM cat WHERE catid=1

 

How do I combine the 2 queries to display all the users in the users table but at the same time tag the users in the category table with a specific category?

I having trouble doing this.

 

Thanks

 

Daniel

 

[esport]

I will add the tables and the results I want to display. It might help.

 

users

uidname

1Dan

2Simon

3Matt

4Emma

5Sarah

 

categories

ciduid

11

13

15

 

Now I want to produce the following results so it displays all users and tag the users with the catid 1 for example.

 

uidnamecatid

1Dan1

2Simonnull

3Matt1

4Emmanull

5Sarah1

 

 

Not sure how to do this.

Thanks in advance.

Daniel

 

[teng84]

select table1.uid,name,cid from table1

left join table2 on table2.uid = table1.uid

[esport]

I actually have used the left join query on this before. However, the uid may occur multiple times in the category table.

[teng84]

select table1.uid,name,cid from table1

left join table2 on table2.uid = table1.uid  group by table2.uid

 

maybe..

[esport]

Still doesn't produce the results Im after. I think it needs the WHERE clause so I can specify the catid. But if i add this in, it will only list the users that are in that catid. I want all users, but some how tag the users in the specified catid and null the rest of users that aren't. Thanks for your help tho

[fenway]

The left join is correct, just add a where clause.

[esport]

Thanks, if I add the WHERE clause it limits the results of the users. It will only display the users that are assigned to that category. I want to display all users regardless if they are in that category, but I still want to know which ones are in the category.

[esport]

It should also produce all the users even if they all aren't in a category either.

[esport]

Maybe if i put it this way it may help., I want to list all favourites which is easily done. However I want to check each favourite to see if they are in a particular group. Now the way I would usually do this is list all the favourites then run a separate query on each favourite to see if they exist in a particular group. Now if I have a list of say 100 favourites, it will run 100 separate queries. I know it is  possible just to run the 1 query but I’m not to sure how to do it.

 

I have just made an example for argument sake:

favourites

fid name

1 Dan

2 Simon

3 Matt

4 Emma

5 Sarah

 

fav_group

gid fid

1 1

1 3

1 5

2 1

2 4

 

Now I want to produce the following results so it displays all users and tag the users with the gid 1 for example.

fid name gid

1 Dan 1

2 Simon null

3 Matt 1

4 Emma null

5 Sarah 1

 

Now say if i select gid=3, it will still list all favourites but obviously gid would be null.

Hope this helps. Thanks in advance and for your patients. Daniel

[esport]

I got it to work

 

select table1.uid,name,cid from table1
left join table2 on table2.uid = table1.uid AND table2.cat_id=1

 

 

 

Rather in the WHERE cluse.