Simple sql join - banging my head!


Ok have two tables with columns of:

News Tag Lists
- newstagID
- newstagTopic

Tags
- tagID
- newstagID

Now I simply want to return a query with a list of ALL news articles and a column with a value to specify whether there is a tag for this, ie:

newstagID | newstagTopic | Tagged
1 | Sport | 1
3 | Politics | null
4 | Business | 1
etc

SELECT * FROM newsTagList

returns my list, but I'm struggling to work out how to join the extra table.

Posted On: Wednesday 7th of November 2012 02:07:33 PM Total Views:  71
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Query - inner join not retrieving all data

With the query below, it will not get all the data, only the data that has a lab with it: Code: SELECT a.ID, a.assignedID, b.Manufacturer, a.modelNumber, a.serialNumber, e.equipmentType, a.Description, c.Location, d.Status, a.modifiedDate, a.modifiedBy, a.Notes, a.Picture, a.calLab, f.LabName, a.calibrationRequired, a.calDate, a.CalDueDate, a.assetNumber FROM tblEquipments a INNER JOIN tblManufacturers b on a.manufacturer = b.manufacturerID INNER JOIN tblLocation c on a.location = c.locationID INNER JOIN tblStatus d on a.Status = d.statusID INNER JOIN tblEquipment_Type e on a.EquipmentType = e.ID INNER JOIN tblLabs f on a.calLab = f.ID Problem is the last inner join. Not all a.calLab has a value that is in tblLabs. It's either 0 or Null. So, adjusted the last line: Code: INNER JOIN tblLabs f on a.calLab = f.ID or a.calLab Is NULL or a.calLab = 0 Now it brings back over 4000 records when there are only 650. If there is no lab associated with it, it repeats that ID with every lab. Anyone have any suggestions
VIEWS ON THIS POST

60

Posted on:

Wednesday 7th November 2012
View Replies!

How to use a join instead of subquery here...

Hi , I am having two tables tblA and tblB with the same structure with fields Id and Path. Here goes my query. For example there are three records in one table and two records in the second table. I have used the query select * from tblA where path not in (select path from tblB) which returns the records from tblA to which is not having a similar path in tblB. I want to replace the subquery with a JOIN. But dont know how to do it (or is it possible). I have tried to explain the query to the best of my language capabilities... Please revert back if you find it difficult to understand...
VIEWS ON THIS POST

109

Posted on:

Wednesday 7th November 2012
View Replies!

join problem

hi, I have 2 table here. table1 code name 0001 Julian 0002 Melvin 0003 John 0004 Peter table2 code1 code2 code3 0001 0003 0004 0004 0002 0001 I want my result set like these. code1 code2 code3 Julian John Peter Peter Melvin Julian Any one have sugestion or can help me.
VIEWS ON THIS POST

130

Posted on:

Wednesday 7th November 2012
View Replies!

best approach "Where In' or join

I have 2 sets Set1 has few UNIQUE items say workitem Ids 2,3,4 Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8 I need to look for Set1 in Set2 so i get 2,3,4 etc... which is the best way Set2 Left join Set1 OR Select item from Set2 where item in (Set1)
VIEWS ON THIS POST

53

Posted on:

Wednesday 7th November 2012
View Replies!

multiple left joins help?

Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong. These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID) emailRecipients table (emailID_PK, RecipientID, SentToEmail) luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)
VIEWS ON THIS POST

166

Posted on:

Wednesday 7th November 2012
View Replies!

One to many join

There are 3 records in table A. id custid 1 1234 2 2345 3 3456 and 6 records in table B. id date 1 10/01/05 2 10/01/05 2 10/03/05 3 09/30/05 3 10/01/05 3 10/02/05 both of the following SQL statement returns 5 records as long as table B contains two dates for the same id: select a.id, a.custid from a inner join b on a.id = b.id where b.date >= '10/01/05' and b.date = '10/01/05' and b.date
VIEWS ON THIS POST

83

Posted on:

Saturday 10th November 2012
View Replies!

Sql inner join

full query Code: select distinct a.username as groupname, e.userid as loginname, c.[description] as flowname, d.[description] as stepname, b.authorized_flag as accessright from irsysusers a inner join authorizedgroup b on b.groupid = a.userid inner join flowdefinition c on b.flowid = c.flowid inner join stepdefinition d on b.stepid = d.stepid inner join groups e on a.userid = e.groupid where a.isgroup = 1 and d.steptype = 0 and not c.status = 'unreleased' and not b.authorized_flag = 5 order by e.userid, c.[description], d.[description] focus on Code: inner join flowdefinition c on b.flowid = c.flowid inner join stepdefinition d on b.stepid = d.stepid table - column flowdef - flowid, description stepdef - flowid, stepid, description I need to match the flowid and stepid from stepdef so d.description returns the proper stepname. ======================================= the whole query should run as follows select a.userid from irsysusers a where a.isgroup = 1 --gimmie a list of group names a.userid matches b.groupid; exclude records that do not match b.authorized_flag = 5 --gimmie only the groups that have been given access to a flow & step b.flowid matches c.flowid; exclude records that do not match c.status = 'unreleased' --gimmie the names of the released flows b.stepid matches d.stepid; list only where steptype = 0 --gimmie the names of the manual steps This needs to change to gimmie the names of the manual steps for whichever flows a.userid matches e.groupid --gimmie the loginnames of the users in the group
VIEWS ON THIS POST

72

Posted on:

Thursday 15th November 2012
View Replies!

how do i cross join in this situation

I have a itemID returned from 1st select statement.now i want to cross join it with 2 values(original and copy) for printing vouchers... how can i accomplish this without using a temp table what i want is as follows If I get 2 values for itemID as 101 , 201 after joining The end result expected is as 101 Original 201 Original 101 Copy 201 Copy
VIEWS ON THIS POST

58

Posted on:

Thursday 15th November 2012
View Replies!

How do I write a simple SQL 2005 query?

since I cannot just sort by certain things in a table column I need to know how to write a simple SQL query to output something that is true or not true within the table column. But I need it to show all columns with that output.
VIEWS ON THIS POST

107

Posted on:

Tuesday 20th November 2012
View Replies!

please help, I suppose it's a simple question

, I'm doing a very simple database in a csv file, with two columns: "Name" and "Message". I need to have ASP pages that display one particular row/record of the database. For instance, "display01.asp" will always display row 1 of the database, "display02.asp" always row 2 etc... It sounds very simple, but I can't find the code, I only know how to view all the database using "Do While Not objRS.EOF" and "objRS.MoveNext". Also, I'd like that if the row/record the ASP page calls is empty, then the page stays empty, with no error message.
VIEWS ON THIS POST

87

Posted on:

Tuesday 20th November 2012
View Replies!

Help!- simple code advice

I'm definitely a newb, so bear with. in an sql database i have 2 tables, A and B. each is loaded with a phone number. i would like to take A, and compare it to B. If A is anywhere on B, do nothing. If A is nowhere on B, export it to a new table, we'll call C another way would be to have them compare and if A is on B, delete the record. If A is not on B, leave it alone, and now A will be the new table with clean data.
VIEWS ON THIS POST

66

Posted on:

Tuesday 20th November 2012
View Replies!

simple connection question

I'm new to the SQL Server and asp realm, I've worked with Access, but needed more to my database interaction. Anyways, I'm trying to connect to my database and can't seem to get it to work. I set up a DSN, and my permissions all look ok. I was curious if someone could show me the connection string I would use for this. Please keep it simple, I've been looking at tons of links and I still haven't found one that was successful.
VIEWS ON THIS POST

91

Posted on:

Tuesday 20th November 2012
View Replies!

Trouble joining 2 queries

Sorry problem solved
VIEWS ON THIS POST

77

Posted on:

Tuesday 20th November 2012
View Replies!

Sql inner join need help

, I am trying to do the student evaluation and stuck in this problem. Each student evaluates himself and all his friends in the same class. I have a sample table below. On the query, I tried to join the table to itself to get a new column calls "StudentID-Change" to list a student himself and his class mates. But I can't get the right result. SELECT Table1.StudentID, Table1_1.StudentID, Table1.DepartmentID FROM Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.StudentID = Table1_1.StudentID; ID StudentID ClassID 1 22 100 2 33 100 3 44 100 Here is the result I want to see in query. StudentID StudentID-Change ClassID 22 22 100 22 33 100 22 44 100 33 22 100 33 33 100 33 44 100 44 22 100 44 33 100 44 44 100 Can anyone please help What did I do wrong Why cant I get the right result using the that join
VIEWS ON THIS POST

101

Posted on:

Tuesday 20th November 2012
View Replies!

What type join should i USE?

Here's my sample table structure: Job ----- JobID Descripton AssignedTo User ------- UserID UserName Basically, AssignedTo can be either an int or a NULL. I need to return all ofthe jobs with AssignedTo replaced by the UserName. When i used the INNER JOIN i do not get Jobs that have AssignedTo as NULL. Please help. Nick
VIEWS ON THIS POST

205

Posted on:

Tuesday 20th November 2012
View Replies!

Table join to fetch details

everbody, I have table1 userid username 1 peter 2 melissa 3 diedra and table2 userid username 1 peter 2 diedra 3 zach I need to find the entries whose userids are same but the usernames differ i.e. the usernames of userid 2 are different in the 2 tables. What is the query to fetch the results
VIEWS ON THIS POST

104

Posted on:

Tuesday 20th November 2012
View Replies!

Comparing two colums in different tables using a join to find non match?

and gals, I have two tables and need to delete some strays in two related tables. The column names are idProduct and Product_id. I wrote an asp script to import and reverse an import except I had forgotten to reverse one of the two tables. So consiquently there are 151 stray records that I need to find. How would I write a join, or not to find these strays and delete them Thank you so much in advance. cbadchris
VIEWS ON THIS POST

104

Posted on:

Monday 26th November 2012
View Replies!

Multiple joins?

Entity Relationships: Person person_id first_name last_name addr1 addr2 etc Patient person_id med_rec_nbr location_id guar_id I want to have a result set that gives me the demographics from person, but the guarantor information from patient also. Kinda like: [someguid], John, Smith, 103 Main St., [addr2]..., 00001, and then the guarantor information appended, since John Smith is the child of James Smith. James smith is listed in the person table as: [someotherguid], James, Smith, 103 Main St., [addr2]... How do I do two joins to the same table and pull a different set of data in the same rowset I have so far: Code: SELECT P.FIRST_NAME, P.LAST_NAME, P.ADDR1, P.ADDR2, PA.MED_REC_NBR FROM PERSON P INNER JOIN PATIENT PA ON P.PERSON_ID = PA.PERSON_ID But I need to put another join between person and patient based upon p.person_id = pa.guar_id, and grab p.first_name and p.last_name based on this new id in person.
VIEWS ON THIS POST

90

Posted on:

Monday 26th November 2012
View Replies!

probably very simple, saving html in db

what field type in mssql database allows you to save html if not how is this done sorry i know very little. hinch
VIEWS ON THIS POST

92

Posted on:

Monday 26th November 2012
View Replies!

cross database join

A query running fine in QueryAnalyzer creates an error "Invalid object name" when run through a Java connection. The object in error is a table in a database other then the one connected to. Why is this a problem from Java I am using the same login and default database in Java, as in QueryAnalyzer when testing the SQL.
VIEWS ON THIS POST

136

Posted on:

Monday 26th November 2012
View Replies!