Query 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query 2 tables

Author

Topic

rjrferreira
Starting Member

13 Posts

Posted - 2007-03-15 : 10:52:10

I all,

i have a problem with a query.

I have two tables (Software and Licenses), and one software can have one or more licenses.

So what i want to get from this query is:

- all info of the software table
- and, if the software have or not, attributed licenses.

How can i do that?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-15 : 10:54:25

Use an outer join

SELECT *
FROM Software
LEFT OUTER JOIN Licenses ON Software.PKID = Licenses.FKID

rjrferreira
Starting Member

13 Posts

Posted - 2007-03-15 : 10:59:09

sorry snSQL but doesn't work that query!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-15 : 11:01:45

So you just tried to run that query and it didn't work - huh! You did not give the names of the columns in your tables, so of course it won't work, you'll need to replace the names in the query I gave you with your names. That was just an example!

rjrferreira
Starting Member

13 Posts

Posted - 2007-03-15 : 11:10:09

quote:
Originally posted by snSQL

So you just tried to run that query and it didn't work - huh! You did not give the names of the columns in your tables, so of course it won't work, you'll need to replace the names in the query I gave you with your names. That was just an example!

I know that!!! :D

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 11:15:46

then post your table DDL, some sample data and the expected result.

rjrferreira
Starting Member

13 Posts

Posted - 2007-03-15 : 11:28:06

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:33:41

SELECT s.ID, s.SoftName, s.Version, s.State, l.ID, l.Serial, l.UserName
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID

Peter Larsson
Helsingborg, Sweden

rjrferreira
Starting Member

13 Posts

Posted - 2007-03-15 : 11:39:55

quote:
Originally posted by Peso

SELECT s.ID, s.SoftName, s.Version, s.State, l.ID, l.Serial, l.UserName
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID

Peter Larsson
Helsingborg, Sweden

rjrferreira
Starting Member

13 Posts

Posted - 2007-03-15 : 11:41:56

quote:
Originally posted by Peso

SELECT s.ID, s.SoftName, s.Version, s.State, l.ID, l.Serial, l.UserName
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID

Peter Larsson
Helsingborg, Sweden

With that query all licenses appears, but i don't want that!!!

I want that one colunm of the result say to me:

"This software don't have any attributed license, or have."

and i just want one line per software.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:50:18

SELECT s.SoftName, s.Version, case when l.softid is null then 'No license' else 'License' end
FROM Software AS s
LEFT JOIN Licenses AS l ON l.SoftID = s.ID

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-15 : 13:09:21

quote:
With that query all licenses appears, but i don't want that!!!
I want that one colunm of the result say to me:
"This software don't have any attributed license, or have."

That's not what you said, you said you wanted the allLicensesFree column, but you didn't tell us how you know if a license is free. So here is what you want without the allLicensesFree column

SELECT Q.[ID], Q.softname, Q.version, Q.state, 
    CASE WHEN Q.Licensed = 0
    THEN 'This software don''t have any attributed license'
    ELSE 'This software do have any attributed license' as licensed
(SELECT S.[ID], S.softname, S.version, S.state, MAX(ISNULL(L.SoftID, 0)) AS Licensed
FROM Software S
LEFT OUTER JOIN Licenses L ON S.[ID] = L.SoftID
GROUP BY S.[ID], S.softname, S.version, S.state) AS Q

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources