Tag Archives: join

Postgres update from left join

Questions: I’m new in PostgreSQL and trying to convert a query from SQL Server. I have a table Users with, among others, the columns bUsrActive, bUsrAdmin and sUsrClientCode. I want to update Users and set bUsrActive = false if there does not exist a another user with the same sUsrClientCode where bUsrAdmin = true and… Read More »

SQL do inner join if condition met

Questions: i want a good way to improve my sql code, i have to use inner join when condition is met. I am currently replicates the code: @SystemMerge bit if (@SystemMerge=1) BEGIN SELECT ……. FROM myTable INNER JOIN table ON table.param1=myTable.param1 INNER JOIN systemTable on systemTable.param2=myTable.param2 END ELSE BEGIN SELECT ……. FROM myTable INNER JOIN… Read More »

SQL Join on nearest available date

Questions: I currently have these tables: CREATE TABLE #SECURITY_TEMP (ID CHAR(30)) CREATE TABLE #SECURITY_TEMP_PRICE_HISTORY (ID CHAR(30), PRICEDATE DATE, PRICE FLOAT) CREATE TABLE #SECURITY_POST (ID CHAR(30), SECPOS int) INSERT INTO #SECURITY_TEMP (ID) VALUES (‘APPL’) ,(‘VOD’),(‘VOW3’), (‘AAA’) INSERT INTO #SECURITY_TEMP_PRICE_HISTORY (ID,PRICEDATE, PRICE) VALUES (‘APPL’, ‘20150101’,10.4), (‘APPL’, ‘20150116’,15.4), (‘APPL’, ‘20150124’,22.4), (‘VOD’, ‘20150101’, 30.5), (‘VOD’, ‘20150116’,16.5), (‘VOD’, ‘20150124’,16.5), (‘VOW3’,… Read More »

Update with inner join?

Questions: I have two databases: DB1 and DB2 How to do something like: update myServer.DB1.dbo.hotels.Name = myServer.DB2.dbo.hotels.Name join myServer.DB2.dbo.hotels on myServer.DB2.dbo.hotels.Code= myServer.DB1.dbo.hotels.Code where myServer.DB2.dbo.hotels.CountryCoe != myServer.DB1.dbo.hotels.CountryCode Answers: UPDATE h1 SET h1.name = h2.name FROM myServer.DB1.dbo.hotels h1 JOIN myServer.DB2.dbo.hotels h2 ON h2.Code = h1.Code WHERE h1.CountryCode <> h2.CountryCode Questions: Answers: example update h1 set h1.Name =… Read More »

How to do left join in Doctrine?

Questions: This is my function where I’m trying to show the User history. For this I need to display the user’s current credits along with his credit history. This is what I am trying to do: public function getHistory($users) { $qb = $this->entityManager->createQueryBuilder(); $qb->select(array(‘a’,’u’)) ->from(‘Credit\Entity\UserCreditHistory’, ‘a’) ->leftJoin(‘User\Entity\User’, ‘u’, \Doctrine\ORM\Query\Expr\Join::WITH, ‘a.user = u.id’) ->where(“a.user = $users… Read More »

SQL Server Query LEFT JOIN, SUM and GROUP BY and I'm stumped!

Questions: I am beating my brain against this one I have 3 SQL Server 2005 tables userawards: id, awardamount, userid, dateawarded, awardtypeid user: id, firstname, lastname awardtypes: id, title So if the awards table had the rows 1, 300.00, 3, 01-01-2011, 1 2, 125.00, 3, 01-05-2011, 1 3, 50.00, 2, 01-05-2011, 2 user table rows… Read More »