Sql 2 Tables Q?

Question

I have 2 tables JObs id,description,name TEST id,jobid I have created the query:

SELECT jobs.id,jobs.name,jobs.country,jobs.description,test.id,test.jobid
FROM jobs,test 
WHERE jobs.userid='10'
AND 
GROUP BY jobs.id

It echos me all jobs but test id is in all 1 If I make it where jobs.id=test.jobid the result is known. How Can I print all jobs where test.jobid is equal to job.id and jobs that don't have a test.jobid

you're doing a cartesian join: you have NO restrictions on how your ttwo tables should be joined, so mysql is joining each record in `jobs` against EVERY record in `test`. that means you're getting `n x m` results. — Marc B, Sep 23 '16 at 17:50

Hogan · Answer 1 · 2016-09-23T18:01:46.000

1

what you had was a cross join. This is not what you want. You want a left join.

Like this

SELECT jobs.id,jobs.name,jobs.country,jobs.description,test.id,test.jobid
FROM jobs
left join test on jobs.id = test.jobid
WHERE jobs.userid='10'
GROUP BY jobs.id

edited Sep 23 '16 at 18:01

answered Sep 23 '16 at 17:49

Hogan

69,564
10
76
117

1

You better put more in that GROUP BY statement... – kjmerf Sep 23 '16 at 17:54
@kbball -- this is mysql -- it lets you be lazy. – Hogan Sep 23 '16 at 17:55
1

thanks only edit jobs.id=test.jobid – Demotivated Sep 23 '16 at 17:57

score 0 · Accepted Answer · answered Sep 23 '16 at 18:02

It sounds like what you're looking for is a left join command.

SELECT jobs.id AS jobs_id,jobs.name,jobs.country,jobs.description,test.id AS test_id,test.jobid AS job_id_from_test
    FROM jobs
    LEFT JOIN test ON test.jobid = jobs.id
    WHERE jobs.userid='10'

-Might help. The above code adds the left join command and takes out the unnecessary 'AND' and 'GROUP BY' statements. LEFT JOIN, unlike a normal JOIN, will return all applicable values for the jobs table, even if there isn't a corresponding row in the test table.

Sql 2 Tables Q?

2 Answers2