Jobs Simply

How do I query 2 MySQL tables from the same database?

I have 2 tables, CurrentJobs and ItemTickets. Both tables have 'Reps'. I am trying to make query both tables to find out in 1 query what jobs have a certain Rep assigned to them. Here is my query that I have tried so far... SELECT * FROM CurrentJobs, ItemTickets where CurrentJobs.Rep = 'Jared' && ItemTickets.Rep = 'Jared' There are 4 rows in each table so I would think it would return 8 total rows, but it returns 16? I know it's something I don't understand about the join feature, but I tried to study different types of joins and I am even more confused. I need it to return the whole row of data and a lot of the other joins just return the requested field? Please Help. Thanks! Thanks for the response SmartSpider, but that did the same as my first query, it pulled 16 results instead of 8? I am getting more confused by the minute???

Public Comments

1. Use the column names instead and specify which table these col names are to be taken from. E.g. Select a.col1, a.col2, b.col1, b.col3 from CurrentJobs a, ItemTickets b Where a.Rep='Jared' AND a.Rep=b.Rep; HTH.
2. Perhaps an example would help: CurrentJobs (Job,Rep) 1,A 2,A 3,A 4,B ItemTickets (Job,Rep) 6,A 7,A 8,C 9,C There are 16 possiple pairs, that look like: (CurrentJobs.Job,CurrentJobs.Rep,ItemTickets.Job,ItemTickets.Rep) 1,A,6,A 1,A,7,A 1,A,8,C 1,A,9,C 2,A,6,A 2,A,7,A 2,A,8,C 2,A,9,C 3,A,6,A 3,A,7,A 3,A,8,C 3,A,9,C 4,B,6,A 4,B,7,A 4,B,8,C 4,B,9,C Of these, 6 have Rep = 'A' for both CurrentJobs and ItemTickets: 1,A,6,A 1,A,7,A 2,A,6,A 2,A,7,A 3,A,6,A 3,A,7,A This is what your query is doing. It is effectively getting all pairs and then limiting the result set to those that have a specific Rep in both tables. If you wanted only 5, you would need a query that looks like: select c.Job, c.Rep from CurrentJobs c where c.Rep = 'A' union select i.Job, i.Rep from ItemTickets i where i.Rep = 'A' This is why you are seeing 16 (4x4) instead of 8 (4+4). You may want to rethink your data model.